package com.geralt.im.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.geralt.im.connect.DBUtil2;
import com.geralt.im.model.Customer;

public class CustomerDaoImlp {
	public int persist(Customer customer) {
		int count = 0;
		Connection conn = DBUtil2.getConnection();
		try {
			String sql = new String("insert into customer values(?,?)");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setString(1, customer.getId());
			ps.setString(2, customer.getPassword());
			count = ps.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return count;
	}
	public int delete(Customer customer) {
		int count = 0;
		Connection conn = DBUtil2.getConnection();
		try {
			String sql = new String("delete from customer where customer_id =?");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			ps.setString(1, customer.getId());
			count = ps.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return count;
	}
	public int update(Customer customer) {
		Connection conn = DBUtil2.getConnection();
		int count = 0;
		try {
			String sql = new String("update customer set customer_id=?,pass_word=? where customer_id = ?");
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, customer.getId());
			ps.setString(2, customer.getPassword());
			ps.setString(3, customer.getId());
			count = ps.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}
	public Customer query(String customerId) {
		Connection conn = DBUtil2.getConnection();
		Customer customer = null;
		try {
			String sql = new String("select * from customer where customer_id = ?");
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, customerId);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer(rs.getString(1),rs.getString(2));
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				if(conn!=null)
					conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return customer;
	}
	
	public Customer query(String customerId,String customerPassword) {
		Connection conn = DBUtil2.getConnection();
		Customer customer = null;
		try {
			String sql = new String("select * from customer where customer_id = ? and pass_word = ?");
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, customerId);
			ps.setString(2, customerPassword);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer(rs.getString(1),rs.getString(2));
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				if(conn!=null)
					conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}
		return customer;
	}
	
	
	public List<Customer> queryAll() {
		Connection conn = DBUtil2.getConnection();
		Customer customer = null;
		List<Customer> customers = new ArrayList<Customer>();
		try {
			String sql = new String("select * from customer");
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				customer = new Customer(rs.getString(1),rs.getString(2));
				customers.add(customer);
			}
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
		}
		return customers;
	}
	public int persist(List<Customer> blist) {
		int count = 0;
		Connection conn = DBUtil2.getConnection();
		try {
			StringBuilder sql = new StringBuilder("insert into customer values(?,?)");
			PreparedStatement ps = conn.prepareStatement(sql.toString());
			conn.setAutoCommit(false);
			int index = 0;
			for(Customer customer: blist){
				ps.setString(1, customer.getId());
				ps.setString(2, customer.getPassword());
				ps.addBatch();
				index++;
				if(index%1000==0){
					ps.executeBatch();
					conn.commit();
				}
			}
			conn.commit();
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			if(conn!=null){
				try {
					conn.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}
		return count;
	}
}
